﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Nmo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Trace;
using Entity;

namespace DataControls
{
    public class GIANGDAY_Data
    {
        DataBaseAccess dataBaseAccess = new DataBaseAccess();

        public void timkiemtheo_TenLop(DataGridView dtgGiangDay, String tenlop)
        {
            String excuteString = "SELECT G.* FROM GIANGDAY G, LOPHOC L WHERE  L.MALOP = G.MALOP AND L.TENLOP LIKE'%" + tenlop + "'";
            DataTable dataTable = new DataTable();
            dataTable = dataBaseAccess.getDataTable(excuteString);
            dtgGiangDay.DataSource = dataTable;
        }

        public void timkiemtheo_TenGiaoVien(DataGridView dtgGiangDay, String tengiaovien)
        {
            String excuteString = "SELECT G.* FROM GIANGDAY G, GIAOVIEN GV WHERE  GV.MAGV = G.MAGV AND GV.TENGV LIKE'%" + tengiaovien + "'";
            DataTable dataTable = new DataTable();
            dataTable = dataBaseAccess.getDataTable(excuteString);
            dtgGiangDay.DataSource = dataTable;
        }








        public void layten_LopHoc(String malophoc, ComboBox cb)
        {
            int a = 0;
            String excuteString = "SELECT TENLOP FROM LOPHOC WHERE MALOP='" + malophoc + "'";
            String tenlophoc = dataBaseAccess.excuteReader(excuteString);
            for (int i = 0; i < cb.Items.Count; i++)
            {
                cb.SelectedIndex = i;
                if (tenlophoc.Equals(((ListItem)(cb.SelectedItem)).name))
                    a = i;
            }
            cb.SelectedIndex = a;
        }

        public void layten_MonHoc(String mamonhoc, ComboBox cb)
        {
            int a = 0;
            String excuteString = "SELECT TENMH FROM MONHOC WHERE MAMH='" + mamonhoc + "'";
            String tenmonhoc = dataBaseAccess.excuteReader(excuteString);
            for (int i = 0; i < cb.Items.Count; i++)
            {
                cb.SelectedIndex = i;
                if (tenmonhoc.Equals(((ListItem)(cb.SelectedItem)).name))
                    a = i;
            }
            cb.SelectedIndex = a;
        }

        public void layten_GiaoVien(String magiaovien, ComboBox cb)
        {
            int a = 0;
            String excuteString = "SELECT TENGV FROM GIAOVIEN WHERE MAGV='" + magiaovien + "'";
            String tengiaovien = dataBaseAccess.excuteReader(excuteString);
            for (int i = 0; i < cb.Items.Count; i++)
            {
                cb.SelectedIndex = i;
                if (tengiaovien.Equals(((ListItem)(cb.SelectedItem)).name))
                    a = i;
            }
            cb.SelectedIndex = a;
        }

        public float tongtietday(String magiaovien, int hocki, int namhoc)
        {
            float a = 0;
            String excuteString1 = "SELECT COUNT(*) FROM GIANGDAY WHERE MAGV='" + magiaovien +
                                                                    "' AND HOCKI=" + hocki +
                                                                    " AND NAMHOC=" + namhoc;
            String excuteString = "SELECT SUM(SOTIETCHUAN) FROM GIANGDAY WHERE MAGV='" + magiaovien +
                                                                    "' AND HOCKI=" + hocki +
                                                                    " AND NAMHOC=" + namhoc ;
            int solopday = Convert.ToInt16(dataBaseAccess.excuteScalar(excuteString1));
            if (solopday <= 0)
            {
                
                a = 0;
            }
            else
            {                
                    a = (float)Convert.ToDouble(dataBaseAccess.excuteScalar(excuteString));                                
            }
            return a;
        }

        public void hienthi_GiangDay(DataGridView dtgGiangDay,String magiaovien)
        {
            String excuteString = "SELECT L.TENLOP, M.TENMH, G.LOAIGD, G.SOTIETCHUAN FROM GIANGDAY G, LOPHOC L, MONHOC M" +
                                    " WHERE L.MALOP= G.MALOP AND M.MAMH= G.MAMH AND MAGV='"+ magiaovien +"'";
            DataTable dataTable = new DataTable();
            dataTable = dataBaseAccess.getDataTable(excuteString);
            dtgGiangDay.DataSource = dataTable;
        }
    }
}
